use std::time::Duration;

use deadpool::Runtime;
use deadpool_redis::redis::ProtocolVersion as RedisProtocolVersion;
use deadpool_redis::{ConnectionAddr, ConnectionInfo, Pool, PoolConfig, RedisConnectionInfo};
pub type RedisPool = Pool;

pub fn redis_conn_pool_from_env() -> anyhow::Result<RedisPool> {
    dotenvy::dotenv().expect("没有.env文件");

    let db_url = std::env::var("REDIS_HOST").expect("REDIS_HOST must be set");

    let port = std::env::var("REDIS_PORT").unwrap_or("6379".to_string());
    let port = port.parse::<u16>()?;

    let db = std::env::var("REDIS_DB").unwrap_or("0".to_string());
    let db = db.parse::<i64>()?;

    let username = std::env::var("REDIS_USERNAME")
        .map(|s| Some(s))
        .unwrap_or(None);

    let password = std::env::var("REDIS_PASSWORD")
        .map(|s| Some(s))
        .unwrap_or(None);

    redis_conn_pool(db_url, Some(port), Some(db), username, password)
}

pub fn redis_conn_pool(
    db_url: String,
    port: Option<u16>,
    db: Option<i64>,
    username: Option<String>,
    password: Option<String>,
) -> anyhow::Result<RedisPool> {
    let mut conn = RedisConnectionInfo::default();
    conn.db = db.unwrap_or(0_i64);
    conn.password = password;
    conn.username = username;

    let conn_cfg = ConnectionInfo {
        addr: ConnectionAddr::Tcp(db_url, port.unwrap_or(6379_u16)),
        redis: conn,
    };

    let pool_cfg = PoolConfig::default();

    let config = deadpool_redis::Config {
        url: None,
        connection: Some(conn_cfg),
        pool: Some(pool_cfg),
    };

    let pool = config.create_pool(Some(Runtime::Tokio1))?;

    Ok(pool)
}

use redis::ProtocolVersion;
use sea_orm::{
    ConnectOptions, Database, DatabaseBackend, DatabaseConnection, Statement, Value, Values,
};
use sqlx::{postgres::PgPoolOptions, Postgres};
use tracing::debug;
pub async fn db_connect_from_env() -> anyhow::Result<DatabaseConnection> {
    dotenvy::dotenv().expect("没有.env文件");

    let db_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");

    let mut opt = ConnectOptions::new(db_url.as_str());
    opt.max_connections(100)
        .min_connections(5)
        .connect_timeout(Duration::from_secs(8))
        .acquire_timeout(Duration::from_secs(8))
        .idle_timeout(Duration::from_secs(8))
        .max_lifetime(Duration::from_secs(8))
        .sqlx_logging(true);

    let conn = Database::connect(opt).await?;
    Ok(conn)
}

pub async fn sqlx_conn_pool() -> anyhow::Result<sqlx::Pool<sqlx::Postgres>> {
    dotenvy::dotenv().expect("没有.env文件");

    let db_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect(&db_url)
        .await
        .unwrap();
    Ok(pool)
}

pub struct RawSqlQueryBuilder {
    segments: Vec<(String, Option<Value>)>,
}

impl RawSqlQueryBuilder {
    pub fn new() -> Self {
        let segments = vec![];

        Self { segments }
    }

    pub fn push(&mut self, sql: &str) -> &mut Self {
        self.segments.push((sql.to_string(), None));
        self
    }

    pub fn push_bind(&mut self, sql: &str, value: Value) -> &mut Self {
        self.segments.push((sql.to_string(), Some(value)));
        self
    }

    pub fn build(&self, base: &str, order_by: Option<&str>) -> (String, Values) {
        let mut lines = format!("{} where 1=1 ", base);
        let mut values = vec![];
        let mut index = 1_usize;
        for (s, v) in &self.segments {
            match v {
                Some(_v) => {
                    values.push(_v.clone());
                    lines.push_str(&format!(" {s}${index} "));
                    index += 1;
                }
                None => {
                    lines.push_str(&s);
                }
            }
        }

        if let Some(order_by) = order_by {
            lines.push_str(&format!(" order by {order_by} "));
        }

        debug!("[BUILD] sql={}, values={:?}", lines, values);

        (lines, Values(values))
    }
}

pub fn from_sql_and_values<I, T>(sql: T, values: I) -> Statement
where
    I: IntoIterator<Item = Value>,
    T: Into<String>,
{
    Statement::from_sql_and_values(DatabaseBackend::Postgres, sql, values)
}

pub fn from_string_values_tuple<I, T>(tuple: (T, I)) -> Statement
where
    I: IntoIterator<Item = Value>,
    T: Into<String>,
{
    from_sql_and_values(tuple.0, tuple.1)
}
